import time
import numpy as np
import pymysql 
import pandas as pd
from sklearn.preprocessing import MinMaxScaler
from joblib import dump
import requests
import datetime

class WeatherUtils(object):
    """天气类"""

    def __init__(self):
        self.date_list = [
            '2024-07-01',
            '2024-08-01',
            '2024-09-01',
            '2024-10-01',
            '2024-11-01',
            '2024-12-01'
        ]
        self.url = 'http://v1.yiketianqi.com/api'

    def get_data(self):
        """获取天气数据"""
        data_list = []
        for date in self.date_list:
            conf = {
                "appid": "89586255",  # 用自己注册的appid
                "appsecret": "YVGfXGH6",  # 用自己注册的appsecret
                "version": "history",
                "year": date[:4],
                "month": date[5:7],
                "city": "南昌"
            }
            # 发起请求获取数据
            res = requests.get(self.url, params=conf)
            ret_data = res.json()
            for d in ret_data['data']:
                data_list.append({
                    'date': datetime.datetime.strptime(d['ymd'], '%Y-%m-%d'),
                    'bwendu': d.get('bWendu', d.get('bwendu', '')),  # 修复：兼容不同键名
                    'ywendu': d.get('yWendu', d.get('ywendu', '')),  # 修复：兼容不同键名
                    'tianqi': d['tianqi'],
                    'fengli': d['fengli'],
                })
        df = pd.DataFrame(data_list)
        df.to_csv('weather.csv', index=False)

class MysqlUtils(object):

    def __init__(self) -> None:
        self.conn = pymysql.connect(
            host= '127.0.0.1',
            user= 'root',
            password= 'root',
            database= 'scenic2',
            port= 3306,          # 明确指定端口
            charset= 'utf8mb4'   # 添加字符集设置
        )
        # 修复：初始化时读取天气数据
        try:
            self.weather_df = pd.read_csv('weather.csv')
        except FileNotFoundError:
            self.weather_df = pd.DataFrame()
        
    def is_holiday(self, date):
        """是否节假日判断
        """
        # 修复：处理datetime对象
        date_str = date.strftime('%Y-%m-%d') if isinstance(date, datetime.datetime) else date
        if date_str in ['2024-09-03', '2024-10-01', '2024-10-02', '2024-10-03', '2024-10-04', '2024-10-05', '2024-10-06', '2024-10-07', '2025-01-01', '2025-01-02', '2025-01-03']:
            return 1
        return 0
        
    def get_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT DATE(g.create_time) as date, HOUR(g.create_time) as hour, count(*) as count 
        FROM order_user_gate_rel g WHERE HOUR(g.create_time) BETWEEN 6 and 23 GROUP BY date, hour
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        # print(df)
        # 格式转换
        self.weather_df['date'] = pd.to_datetime(self.weather_df['date'])
        df['date'] = pd.to_datetime(df['date'])
        df_pivot = pd.merge(df, self.weather_df, on='date')
        df_pivot.set_index('date', inplace=True)
       
        df_pivot['dow'] = df_pivot.index.dayofweek # 星期几（0-6）
        df_pivot['month'] = df_pivot.index.month # 月份
        df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)
        # print(df_pivot.head)
        
        # 对星期几和月份，天气和风力进行独热编码
        df_pivot = pd.get_dummies(df_pivot, columns=['dow', 'month', 'tianqi', 'fengli'], dtype=int)
        
        # 对温度进行类型转换
        df_pivot['bwendu'] = df_pivot['bwendu'].astype(str).str.replace('°', '').astype(int)
        df_pivot['ywendu'] = df_pivot['ywendu'].astype(str).str.replace('°', '').astype(int)
        
        # 归一化入园数
        scaler = MinMaxScaler()
        features = df_pivot[['count']]
        df_pivot['count'] = scaler.fit_transform(features)
        dump(scaler, 'scaler.joblib')
        
        # 归一化天气
        weather_features = df_pivot[['bwendu', 'ywendu']]
        df_pivot[['bwendu', 'ywendu']] = scaler.fit_transform(weather_features)
        dump(scaler, 'scaler_weather.joblib')
        
        df_pivot.to_csv('scenic_data.csv', index=False)

        
        
if __name__ == '__main__':
    wu = WeatherUtils()
    wu.get_data()
    mu = MysqlUtils()  # 修复：注释符号错误
    mu.get_data()      # 修复：逗号改为点号